package com.samebuilding.dao.impl;

import java.sql.SQLException;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;

import com.samebuilding.dao.CommentDAO;
import com.samebuilding.domain.Comment;

public class CommentDAOImpl extends GenericDAOImpl<Comment, Integer> implements
		CommentDAO {

	private static final String DELETE_BY_ID_AND_RECEIVEUSERNO = "delete from Comment where id = ? and receiveUserNo = ?";
	private static final String DELETE_BY_ID_AND_SENDUSERNO = "delete from Comment where id = ? and sendUserNo = ?";
	private static final String GET_ALL_COMMENT_I_RECEIVE = "from Comment where receiveUserNo = ?";
	private static final String GET_COUNT_ALL_COMMENT_I_RECEIVE = "select count(id) from Comment where receiveUserNo = ?";
	private static final String GET_ALL_COMMENT_I_SEND = "from Comment where sendUserNo = ?";
	private static final String GET_COUNT_ALL_COMMENT_I_SEND = "select count(id) from Comment where sendUserNo = ?";
	private static final String GET_COMMENT_I_RECEIVE_BY_TYPE ="from Comment where receiveUserNo = ? and type = ?";
	private static final String GET_COMMENT_I_SEND_BY_TYPE = "from Comment where sendUserNo = ? and type = ?";
	private static final String GET_COUNT_COMMENT_I_RECEIVE_BY_TYPE = "select count(id) from Comment where receiveUserNo = ? and type = ?";
	private static final String GET_COUNT_COMMENT_I_SEND_BY_TYPE = "select count(id) from Comment where sendUserNo = ? and type = ?";
	private static final String GET_COUNT_UNREAD_COMMENT = "select count(id) from Comment where receiveUserNo = ? and readed = 0";
	private static final String UPDATE_UNREAD_TO_READ = "update Comment set readed = 1 where receiveUserNo = ? and readed = 0";
	private static final String GET_COMMENT_BY_TYPE = "from Comment where foreignId = ? and type = ?";
	private static final String GET_COUNT_COMMENT_BY_TYPE = "select count(id) from Comment where foreignId = ? and type = ?";
	
	
	public void deleteByIdAndReceiveUserNo(final Integer id, final Integer receiveUserNo) {
		
		getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session s) throws HibernateException,
					SQLException {
				Query query = s.createQuery(DELETE_BY_ID_AND_RECEIVEUSERNO);
				query.setInteger(0, id);
				query.setInteger(1, receiveUserNo);
				query.executeUpdate();
				return null;
			}
		});

	}

	public void deleteByIdAndSendUserNo(final Integer id, final Integer sendUserNo) {
		getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session s) throws HibernateException,
					SQLException {
				Query query = s.createQuery(DELETE_BY_ID_AND_SENDUSERNO);
				query.setInteger(0, id);
				query.setInteger(1, sendUserNo);
				query.executeUpdate();
				return null;
			}
		});

	}

	@SuppressWarnings("unchecked")
	public List<Comment> getAllCommentIReceive(final Integer userNo,final int start,
			final int limit) {
		List<Comment> list = getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(GET_ALL_COMMENT_I_RECEIVE);
				query.setInteger(0, userNo);
				query.setFirstResult(start);
				query.setMaxResults(limit);
				List<Comment> list = query.list();
				return list;
			}
		});
		return list;
	}

	@SuppressWarnings("unchecked")
	public List<Comment> getAllCommentISend(final Integer userNo,final int start,final int limit) {
		List<Comment> list = getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(GET_ALL_COMMENT_I_SEND);
				query.setInteger(0, userNo);
				query.setFirstResult(start);
				query.setMaxResults(limit);
				List<Comment> list = query.list();
				return list;
			}
		});
		return list;
	}

	@SuppressWarnings("unchecked")
	public List<Comment> getCommentIReceiveByType(final Integer userNo, final Integer type,
			final int start, final int limit) {
		List<Comment> list = getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(GET_COMMENT_I_RECEIVE_BY_TYPE);
				query.setInteger(0, userNo);
				query.setInteger(1, type);
				query.setFirstResult(start);
				query.setMaxResults(limit);
				List<Comment> list = query.list();
				return list;
			}
		});
		return list;
	}

	@SuppressWarnings("unchecked")
	public List<Comment> getCommentISendByType(final Integer userNo,final Integer type,
			final int start,final int limit) {
		List<Comment> list = getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(GET_COMMENT_I_SEND_BY_TYPE);
				query.setInteger(0, userNo);
				query.setInteger(1, type);
				query.setFirstResult(start);
				query.setMaxResults(limit);
				List<Comment> list = query.list();
				return list;
			}
		});
		return list;
	}

	public Integer getCountAllCommentIReceive(final Integer userNo) {
		long count = (Long)this.getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(GET_COUNT_ALL_COMMENT_I_RECEIVE);
				query.setInteger(0, userNo);

				return query.uniqueResult();
			}
		});
		
		return (int)count;
	}

	public Integer getCountAllCommentISend(final Integer userNo) {
		long count  = (Long)this.getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(GET_COUNT_ALL_COMMENT_I_SEND);
				query.setInteger(0, userNo);

				return query.uniqueResult();
			}
		});
		return (int)count;
	}

	public int getCountCommentIReceiveByType(final Integer userNo,
			final Integer type) {
		long count = (Long)this.getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(GET_COUNT_COMMENT_I_RECEIVE_BY_TYPE);
				query.setInteger(0, userNo);
				query.setInteger(1, type);
				return query.uniqueResult();
			}
		});
		
		return (int)count;
	}

	public int getCountCommentISendByType(final Integer userNo, final Integer type) {
		long count = (Long)this.getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(GET_COUNT_COMMENT_I_SEND_BY_TYPE);
				query.setInteger(0, userNo);
				query.setInteger(1, type);
				return query.uniqueResult();
			}
		});
		
		return (int)count;
	}

	public int getCountUnreadComment(final Integer receiveUserNo) {
		long count = (Long)this.getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(GET_COUNT_UNREAD_COMMENT);
				query.setInteger(0, receiveUserNo);
				return query.uniqueResult();
			}
		});
		
		return (int)count;
	}

	public void updateUnreadToRead(final Integer receiveUserNo) {
		getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session s) throws HibernateException,
					SQLException {
				Query query = s.createQuery(UPDATE_UNREAD_TO_READ);
				query.setInteger(0, receiveUserNo);
				query.executeUpdate();
				return null;
			}
		});

	}

	@SuppressWarnings("unchecked")
	public List<Comment> getCommentByType(final Integer foreignId, final Integer type, final int start, final int limit) {
		List<Comment> list = getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(GET_COMMENT_BY_TYPE);
				query.setInteger(0, foreignId);
				query.setInteger(1, type);
				query.setFirstResult(start);
				query.setMaxResults(limit);
				List<Comment> list = query.list();
				return list;
			}
		});
		return list;
	}

	public int getCountCommentByType(final Integer foreignId, final Integer type) {
		long count = (Long)this.getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(GET_COUNT_COMMENT_BY_TYPE);
				query.setInteger(0, foreignId);
				query.setInteger(1, type);
				return query.uniqueResult();
			}
		});
		
		return (int)count;
	}


}
